package com.zl_huang.example.dao;


import org.springframework.beans.factory.annotation.Autowired;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;
import com.zl_huang.example.model.User;

@Repository
public class UserDaoImpl implements IUserDao {
	
	@Autowired
	private JdbcTemplate jdbcTemplate;
	
//===========================查询某一学员方法	===========================
	@Override
	public User getUserById(String user_id){
		String sql = "select user_id,user_name,password,tel,email,address,birthday,gender,last_lock_time,login_fail_count from exam_user where user_id = ?";
		
		return jdbcTemplate.queryForObject(sql, new Object[]{user_id}, new RowMapper<User>(){
			public User mapRow(ResultSet rs,int num)throws SQLException{
				User user = new User();
				user.setUser_id(rs.getString("user_id"));
				user.setUser_name(rs.getString("user_name"));
				user.setPassword(rs.getString("password"));
				user.setTel(rs.getString("tel"));
				user.setEmail(rs.getString("email"));
				user.setAddress(rs.getString("address"));			
				user.setBirthday(rs.getString("birthday"));
				user.setGender(rs.getString("gender"));
				user.setLast_lock_time(rs.getDate("last_lock_time"));
				user.setLogin_fail_count(rs.getInt("login_fail_count"));
				
				
				return user;
			}
		});
	}
	
//==================查询所有学员方法==============================	
	@Override
	public List<User> findAllUsers(){
		String sql = "select user_id,user_name,gender,birthday,tel,address from exam_user";
		List<User> userList = jdbcTemplate.query(sql,new RowMapper<User>(){
			public User mapRow(ResultSet rs,int num)throws SQLException{
				User user = new User();
				user.setUser_id(rs.getString("user_id"));
				user.setUser_name(rs.getString("user_name"));
				user.setGender(rs.getString("gender"));
				user.setBirthday(rs.getString("birthday"));
				user.setTel(rs.getString("tel"));
				user.setAddress(rs.getString("address"));
				return user;
			}
		});
		return userList;
	}
		
		
//=============新增学员方法============================
	@Override
	public Integer createUser(User user) {	
		
		String sql = "insert into exam_user(user_id,user_name,password,tel,email,address,birthday,gender,last_lock_time,login_fail_count)"+
	 " values(?,?,?,?,?,?,?,?,?,0)";
		
		return jdbcTemplate.update(sql, new Object[]{user.getUser_id(),user.getUser_name(),user.getPassword(),
			user.getTel(),user.getEmail(),user.getAddress(),user.getBirthday(),(user.getGender().equals("男"))? 0:1,new SimpleDateFormat("yyyy-MM-dd").format(new Date(System.currentTimeMillis()))});
		
	}

	
//====================修改学员方法=========================
	@Override
	public Integer editUser(User user) throws Exception {
		
			
		String sql = "update exam_user set user_name = ?,password = ?,tel = ?,email = ?,address = ?,birthday = ?,gender = ?,last_lock_time = ? where user_id ="+user.getUser_id();
		
		return jdbcTemplate.update(sql, new Object[]{user.getUser_name(),user.getPassword(),
				user.getTel(),user.getEmail(),user.getAddress(),user.getBirthday(),(user.getGender().equals("男"))? 0:1,new SimpleDateFormat("yyyy-MM-dd").format(new Date(System.currentTimeMillis()))});
	
	}
	
//=================删除学员方法==============================
	@Override
	public Integer deleteUser(String user_id) throws Exception {
		String sql = "delete from exam_user where user_id = ?";
		return jdbcTemplate.update(sql, new Object[]{user_id});
		
	}

}
